﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace prjKQXS_DLL.Security
{
    public class clsUser
    {
        //Cài đặt event OnError để bắt các lỗi chưa được xử lý
        public delegate void OnErrorHandler(Exception e);
        public event OnErrorHandler OnError;

        //Cài đặt event OnAction để thông báo các trạng thái kết quả
        public delegate void OnActionHandler(SqlException e);
        public event OnActionHandler OnAction;

        /// <summary>
        /// Kiểm tra xem một UserName đã tồn tại hay chưa
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        public bool isExistUser(string username)
        {
            bool retVal = false;

            SqlConnection conn = new SqlConnection(BienTc.connString);
            conn.Open();
            SqlCommand comm = new SqlCommand("select UserName from tblUser where UPPER(UserName) = @UserName", conn);

            try
            {
                comm.Parameters.AddWithValue("@UserName", username.ToUpper());
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read()) retVal = true;
            }
            catch (SqlException e)
            {
                OnAction(e);
            }
            catch (Exception e)
            {
                OnError(e);
            }

            return retVal;
        }

        /// <summary>
        /// Thêm một user, nếu lưu thành công thì trả về id của user vừa thêm.
        /// </summary>
        /// <param name="FullName"></param>
        /// <param name="UserName"></param>
        /// <param name="Password"></param>
        /// <param name="Supervisor"></param>
        /// <param name="Group"></param>
        /// <returns></returns>
        public object Insert(string FullName, string UserName, string Password, string Supervisor, string Group)
        {
            if (clsLicense.checkLicense())
            {
                try
                {
                    SqlConnection conn = new SqlConnection(BienTc.connString);
                    conn.Open();

                    SqlCommand cmd = new SqlCommand("proc_UR_frmUser_Insert", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@FullName", FullName);
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    cmd.Parameters.AddWithValue("@Password", clsCommon.Encrypt(Password, BienTc.encryptString, true));

                    cmd.Parameters.AddWithValue("@Supervisor", Supervisor);
                    cmd.Parameters.AddWithValue("@Group", Group);

                    return cmd.ExecuteScalar();
                }
                catch (SqlException e)
                {
                    OnAction(e);
                }
                catch (Exception e)
                {
                    OnError(e);
                }
            }

            return DBNull.Value;
        }

        public int InsertPermission(int UserID, int FunctionID, bool Allow)
        {
            int result = 0;

            if (clsLicense.checkLicense())
            {
                try
                {
                    SqlConnection conn = new SqlConnection(BienTc.connString);
                    conn.Open();

                    SqlCommand cmd = new SqlCommand("INSERT INTO tblUserPermission(UserID, FunctionID, Allow) VALUES(@UserID, @FunctionID, @Allow)", conn);
                    //cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@UserID", UserID);
                    cmd.Parameters.AddWithValue("@FunctionID", FunctionID);
                    cmd.Parameters.AddWithValue("@Allow", Allow);
                    return cmd.ExecuteNonQuery();
                }
                catch (SqlException e)
                {
                    OnAction(e);
                }
                catch (Exception e)
                {
                    OnError(e);
                }
            }

            return result;
        }

        /// <summary>
        /// Cập nhật thông tin user
        /// </summary>
        /// <param name="id"></param>
        /// <param name="FullName"></param>
        /// <param name="UserName"></param>
        /// <param name="Password"></param>
        /// <param name="Supervisor"></param>
        /// <param name="Group"></param>
        /// <returns></returns>
        public int UpdateUser(int id, string FullName, string UserName, string Password, string Supervisor, string Group)
        {
            int result = 0;

            if (clsLicense.checkLicense())
            {
                try
                {
                    SqlConnection conn = new SqlConnection(BienTc.connString);
                    conn.Open();

                    SqlCommand cmd = new SqlCommand("proc_UR_frmUser_Update", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ID", id);
                    cmd.Parameters.AddWithValue("@FullName", FullName);
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    cmd.Parameters.AddWithValue("@Password", clsCommon.Encrypt(Password, BienTc.encryptString, true));
                    cmd.Parameters.AddWithValue("@Supervisor", Supervisor);
                    cmd.Parameters.AddWithValue("@Group", Group);
                    return cmd.ExecuteNonQuery();
                }
                catch (SqlException e)
                {
                    OnAction(e);
                }
                catch (Exception e)
                {
                    OnError(e);
                }

            }
                
            return result;
        }
        
        /// <summary>
        /// Cập nhật quyền cho user
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="FunctionID"></param>
        /// <param name="Allow"></param>
        /// <returns></returns>
        public int UpdatePermission(int UserID, int FunctionID, bool Allow)
        {
            int result = 0;

            if (clsLicense.checkLicense())
            {
                try
                {
                    SqlConnection conn = new SqlConnection(BienTc.connString);
                    conn.Open();

                    SqlCommand cmd = new SqlCommand("UPDATE tblUserPermission SET Allow = @Allow WHERE UserID = @UserID and FunctionID = @FunctionID", conn);
                    //cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Allow", Allow);
                    cmd.Parameters.AddWithValue("@UserID", UserID);
                    cmd.Parameters.AddWithValue("@FunctionID", FunctionID);
                    return cmd.ExecuteNonQuery();
                }
                catch (SqlException e)
                {
                    OnAction(e);
                }
                catch (Exception e)
                {
                    OnError(e);
                }

            }

            return result;
        }

        /// <summary>
        /// Xóa một tài khoản
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delete(int id)
        {
            int result = 0;

            if (clsLicense.checkLicense())
            {
                try
                {
                    SqlConnection conn = new SqlConnection(BienTc.connString);
                    conn.Open();

                    SqlCommand cmd = new SqlCommand("delete tblUser where ID = @ID", conn);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@ID", id);
                    return cmd.ExecuteNonQuery();
                }
                catch (SqlException e)
                {
                    OnAction(e);
                }
                catch (Exception e)
                {
                    OnError(e);
                }
            }

            return result;
        }

        public DataTable loadPermission(int UserID)
        {
            DataTable dt = new DataTable();
            try
            {
                string sql = @"select FunctionID
	                            , FunctionName
	                            , (select Allow from tblUserPermission P where UserID = @UserID and P.FunctionID = F.FunctionID) as Allow
                            from tblFunction F
                            order by FunctionName";
                SqlConnection conn = new SqlConnection(BienTc.connString);
                conn.Open();

                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.SelectCommand.Parameters.AddWithValue("@UserID", UserID);
                da.Fill(dt);
            }
            catch (SqlException e)
            {
                OnAction(e);
            }
            catch (Exception e)
            {
                OnError(e);
            }

            return dt;
        }
    }
}
